package com.wysql.common.excel;

import com.wysql.common.market.MiddleGround;
import com.wysql.common.model.vo.SysDictVo;
import com.wysql.common.util.HumpLineUtil;
import lombok.Builder;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.*;

@Slf4j
public class ExcelUtils<T> {
    /**
     * 导出
     *
     * @param list          数据
     * @param clz           类型
     * @param titleRowIndex 标题行
     * @param sheet         。。
     * @param rowIndex      起始行 0 开始
     * @throws NoSuchFieldException 没有列
     */
    @SuppressWarnings({"java:S3011"})
    public void exp(List<T> list, Class<?> clz, Sheet sheet, final int rowIndex, CellStyle cellStyle, final int titleRowIndex, List<SysDictVo> sysDictVos) throws NoSuchFieldException, IllegalAccessException {
        if (list == null || list.isEmpty()) {
            return;
        }
        Field[] fields = clz.getFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (excelField.exp()) {
                // 执行导出流程。。。。。。。。。。。。 1 3 5
                fieldNames[excelField.expOrder()] = field.getName();
            }
        }
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat();

        int colIndex = 0;
        // 用于计数
        int rowIndexs = rowIndex;
        for (String fieldName : fieldNames) {
            if (!StringUtils.isEmpty(fieldName)) {
                Field field = clz.getField(fieldName);
                field.setAccessible(true);
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                if (excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
                Row titleRow = sheet.getRow(rowIndexs);
                if (titleRow == null) {
                    titleRow = sheet.createRow(titleRowIndex);
                }
                Cell titleCell = titleRow.getCell(colIndex);
                if (titleCell == null) {
                    titleCell = titleRow.createCell(colIndex);
                }
                titleCell.setCellValue(excelField.fieldCN());


                for (T t : list) {
                    Row row = sheet.getRow(rowIndexs);
                    // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
                    if (row == null) {
                        row = sheet.createRow(rowIndexs);
                    }
                    Cell cell = row.getCell(colIndex);
                    if (cell == null) {
                        cell = row.createCell(colIndex);
                    }
                    cell.setCellStyle(cellStyle);
                    if (field.getType().isAssignableFrom(Date.class)) {
                        cell.setCellValue(simpleDateFormat.format((Date) field.get(t)));
                    } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                        cell.setCellValue(((BigDecimal) field.get(t)).doubleValue());
                    } else if (field.getType().isAssignableFrom(Double.class)) {
                        cell.setCellValue((Double) field.get(t));
                    } else {
                        if (StringUtils.isEmpty(excelField.select())) {
                            cell.setCellValue(field.get(t).toString());
                        } else {
                            for (SysDictVo sysDictVo : sysDictVos) {
                                if (sysDictVo.getDictType().equals(excelField.select()) && Objects.equals(field.get(this), sysDictVo.getDictId())) {
                                    cell.setCellValue(sysDictVo.getDictName());
                                }
                            }
                        }
                    }
                    rowIndexs++;
                }
                rowIndexs = rowIndex;
                colIndex++;
            }
        }
    }

    /**
     * 普通格式
     *
     * @param workbook
     * @return
     */
    public CellStyle getStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        Font font = workbook.createFont();
        font.setFontName("宋体");  //字体
        font.setFontHeightInPoints((short) 11);  //设置字体大小
        cellStyle.setFont(font);//选择需要用到的字体格式
        return cellStyle;
    }

    /**
     * 普通格式
     *
     * @param workbook
     * @return
     */
    public CellStyle getStyleRed(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);


        Font font = workbook.createFont();
        font.setFontName("宋体");  //字体
        font.setFontHeightInPoints((short) 11);  //设置字体大小
        cellStyle.setFont(font);//选择需要用到的字体格式
        return cellStyle;
    }

    /**
     * 普通格式
     *
     * @param workbook
     * @return
     */
    public CellStyle getStyleGreen(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Font font = workbook.createFont();
        font.setFontName("宋体");  //字体
        font.setFontHeightInPoints((short) 11);  //设置字体大小
        cellStyle.setFont(font);//选择需要用到的字体格式
        return cellStyle;
    }

    /**
     * 日期格式
     *
     * @param workbook
     * @return
     */
    public CellStyle getStyle(Workbook workbook, String dateFormat) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        Font font = workbook.createFont();


        font.setFontName("宋体");  //字体
        font.setFontHeightInPoints((short) 11);  //设置字体大小
        cellStyle.setFont(font);//选择需要用到的字体格式

        CreationHelper createHelper = workbook.getCreationHelper();

        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(dateFormat));


        return cellStyle;
    }


    /**
     * 单元格设置下拉框格式
     */
    public static void dropDownBox(String[] datas, CellRangeAddressList regions, Sheet sheet) {

        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(datas);
        DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);

        if (createValidation instanceof XSSFDataValidation) {
            createValidation.setSuppressDropDownArrow(true);
            createValidation.setShowErrorBox(true);
        } else {
            createValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(createValidation);
    }

    public static void dateBox(CellRangeAddressList regions, Sheet sheet) {
//        try {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置验证方式
        DataValidationConstraint constraint;
        if (sheet instanceof XSSFSheet) {
            constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "Date(1990, 1, 1)", "Date(2099, 1, 1)", "yyyy/m/d");
        } else {
//                constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,
//                        null
//                        , null
//                        , "yyyy/m/d");
            constraint = helper.createDateConstraint(XSSFDataValidationConstraint.ValidationType.DATE, "2000/01/01", "2099/12/01", "yyyy/m/d");
        }
        //创建验证对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        //错误提示信息
        dataValidation.createErrorBox("提示", "请输入[yyyy/MM/dd]格式日期，范围1990-2099");
        dataValidation.setShowErrorBox(true);
        //验证和工作簿绑定
        sheet.addValidationData(dataValidation);
//        } catch (ParseException parseException) {
//            parseException.printStackTrace();
//        }
    }

    public static void main(String[] args) {
        Workbook workbook = new XSSFWorkbook();
        CellRangeAddressList regions = new CellRangeAddressList(1, 1, 1, 1);
        dateBox(regions, workbook.createSheet());
    }

    /**
     * 导入
     *
     * @param clz        类型
     * @param sheet      。。
     * @param rowIndex   起始行 0 开始
     * @param lastRowNum 最后一行
     * @return
     * @throws NoSuchFieldException 没有列
     */
    public List<T> imp(Class<?> clz, Sheet sheet, final int rowIndex, int lastRowNum, Workbook workbook, List<SysDictVo> sysDictVos) throws NoSuchFieldException, IllegalAccessException, InstantiationException, ParseException {

        List<T> list = new ArrayList<>();

        Field[] fields = clz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        int maxCell = 0;
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (excelField != null && excelField.imp()) {
                // 执行导出流程。。。。。。。。。。。。 1 3 5
                int i = excelField.impOrder();
                if (i > maxCell) {
                    maxCell = i;
                }
                fieldNames[i] = field.getName();
            }
        }
        FormulaEvaluator hssfFormulaEvaluator;
        if (workbook instanceof HSSFWorkbook) {
            hssfFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        } else {
            hssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        }

        // 读取
        for (int i = rowIndex; i < lastRowNum; i++) {
            T t = (T) clz.newInstance();
            Row row = sheet.getRow(i);
            if (row == null || row.getLastCellNum() < maxCell ){
                break;
            }
            for (int i1 = 0; i1 < fieldNames.length; i1++) {
                String fieldName = fieldNames[i1];
                if (!StringUtils.isEmpty(fieldName)) {
                    Field field = clz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    ExcelField excelField = field.getAnnotation(ExcelField.class);

                    System.out.println("第多少行：" + row);
                    System.out.println("报错原因：" + excelField.impOrder());

                    Cell cell = row.getCell(excelField.impOrder());
                    if (cell != null) {
                        Object value = null;
                        switch (cell.getCellType()) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
//                                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
//                                    value = cell.getNumericCellValue();
//                                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                                    value = cell.getNumericCellValue();
//                                } else {
//                                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue());
//                                    SimpleDateFormat f = new SimpleDateFormat("yyyy/MM/dd");
//                                    //  特定格式的日期
//                                    value = f.parse(f.format(date));
//                                }
//                                value = cell.getNumericCellValue();

                                DataFormatter dataFormatter = new DataFormatter();
                                value = dataFormatter.formatCellValue(cell);
                                break;
                            case BOOLEAN:
                                value = cell.getBooleanCellValue();
                                break;
                            case FORMULA:
                                try {
                                    CellValue cellValue = hssfFormulaEvaluator.evaluate(cell);
                                    if (cellValue.getCellType() == CellType.NUMERIC) {
//                                        value = cellValue.getNumberValue();
                                        String s = Double.toString(cellValue.getNumberValue());
                                        value = new BigDecimal(s).setScale(excelField.scale(), RoundingMode.DOWN);
                                    } else if (cellValue.getCellType() == CellType.STRING) {
                                        value = cellValue.getStringValue();
                                    }
                                } catch (IllegalStateException e) {
                                    e.printStackTrace();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                                break;
                            case BLANK:
                                value = null;
                                break;
                            default:
                                value = cell.toString();
                        }
                        try {
                            if (value == null || StringUtils.isEmpty(value.toString())) {
                                value = null;
                            } else if (field.getType().isAssignableFrom(Date.class)) {
                                if (value instanceof String) {
                                    if (((String) value).contains("-")) {
                                        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                        value = simpleDateFormat.parse(value.toString());
                                    } else if (((String) value).contains("/")) {
                                        String reg1 = "\\d{4}/\\d{1,2}/\\d{1,2}";
                                        String reg2 = "\\d{2}/\\d{1,2}/\\d{1,2}";
                                        if (value.toString().matches(reg1)) {
                                            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
                                            value = simpleDateFormat.parse(value.toString());
                                        } else if (value.toString().matches(reg2)) {
                                            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yy/MM/dd");
                                            value = simpleDateFormat.parse(value.toString());
                                        } else {
                                            value = null;
                                        }
                                    }
                                } else {
                                    value = ExcelUtils.DoubleToDate((double) value);
                                }
                            } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                                if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = new BigDecimal(value.toString().equals("-") ? "0" : value.toString());
                                } else {
                                    value = null;
                                }
                            } else if (field.getType().isAssignableFrom(Double.class)) {
                                if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = Double.parseDouble(value.toString());
                                } else {
                                    value = null;
                                }
                            } else if (field.getType().isAssignableFrom(String.class)) {
                                if (!StringUtils.isEmpty(excelField.select())) {
                                    for (SysDictVo sysDictVo : sysDictVos) {
                                        if (sysDictVo.getDictType().equals(excelField.select()) && Objects.equals(value, sysDictVo.getDictName())) {
                                            value = sysDictVo.getDictId();
                                        }
                                    }
                                } else if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = value.toString();
                                } else {
                                    value = null;
                                }
                            } else if (field.getType().isAssignableFrom(Integer.class)) {
                                if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    BigDecimal bigDecimal = new BigDecimal(value.toString());
                                    value = bigDecimal.intValue();
                                } else {
                                    value = null;
                                }
                            } else if (field.getType().isAssignableFrom(Long.class)) {
                                if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = Long.parseLong(value.toString());
                                } else {
                                    value = null;
                                }
                            }


                            field.set(t, value);
                        } catch (Exception e) {
                            log.error("第几行{}{}", i, excelField.fieldCN());
                            e.printStackTrace();
                        }
                    }
                }
            }
            list.add(t);
        }

        return list;
    }

    /**
     * 导入
     *
     * @param clz        类型
     * @param sheet      。。
     * @param rowIndex   起始行 0 开始
     * @param lastRowNum 最后一行
     * @return
     * @throws NoSuchFieldException 没有列
     */
    public List<T> impByName(Class<?> clz, Sheet sheet, final int titleIndex, final int rowIndex, int lastRowNum, Workbook workbook, List<SysDictVo> sysDictVos) throws NoSuchFieldException, IllegalAccessException, InstantiationException, ParseException {

        List<T> list = new ArrayList<>();

        // title  名称 位置

        // field  field 名称


        Field[] fields = clz.getDeclaredFields();
        Map<String, Integer> titleName = new HashMap<>();
        Map<String, String> fieldMap = new HashMap<>();
        List<String> fieldNames = Lists.newArrayList();

        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (excelField != null && excelField.imp()) {
                fieldMap.put(field.getName(), excelField.fieldCN());
                fieldNames.add(field.getName());
            }
        }

        Row titleRow = sheet.getRow(titleIndex);
        short lastCellNum = titleRow.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = titleRow.getCell(i);
            titleName.put(cell.getStringCellValue(), i);
        }

        FormulaEvaluator hssfFormulaEvaluator;
        if (workbook instanceof HSSFWorkbook) {
            hssfFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        } else {
            hssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        }

        // 读取
        for (int i = rowIndex; i < lastRowNum; i++) {
            T t = (T) clz.newInstance();

            for (int i1 = 0; i1 < fieldNames.size(); i1++) {
                String fieldName = fieldNames.get(i);
                if (!StringUtils.isEmpty(fieldName)) {
                    Field field = clz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    ExcelField excelField = field.getAnnotation(ExcelField.class);
                    Row row = sheet.getRow(i);
                    Cell cell = row.getCell(titleName.get(fieldMap.get(fieldName)));
                    if (cell != null) {
                        Object value = null;
                        switch (cell.getCellType()) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
//                                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
//                                    value = cell.getNumericCellValue();
//                                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                                    value = cell.getNumericCellValue();
//                                } else {
//                                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue());
//                                    SimpleDateFormat f = new SimpleDateFormat("yyyy/MM/dd");
//                                    //  特定格式的日期
//                                    value = f.parse(f.format(date));
//                                }
                                value = cell.getNumericCellValue();
                                break;
                            case BOOLEAN:
                                value = cell.getBooleanCellValue();
                                break;
                            case FORMULA:
                                try {
                                    CellValue cellValue = hssfFormulaEvaluator.evaluate(cell);
                                    if (cellValue.getCellType() == CellType.NUMERIC) {
//                                        value = cellValue.getNumberValue();
                                        String s = Double.toString(cellValue.getNumberValue());
                                        value = new BigDecimal(s).setScale(excelField.scale(), RoundingMode.DOWN);
                                    } else if (cellValue.getCellType() == CellType.STRING) {
                                        value = cellValue.getStringValue();
                                    }
                                } catch (IllegalStateException e) {
                                    e.printStackTrace();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                                break;
                            case BLANK:
                                value = null;
                                break;
                            default:
                                value = cell.toString();
                        }
                        try {
                            if (field.getType().isAssignableFrom(Date.class)) {
                                value = value == null ? null : DoubleToDate((double) value);
                            } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                                if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = new BigDecimal(value.toString().equals("-") ? "0" : value.toString());
                                } else {
                                    value = null;
                                }
                            } else if (field.getType().isAssignableFrom(Double.class)) {
                                if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = Double.parseDouble(value.toString());
                                } else {
                                    value = null;
                                }
                            } else if (field.getType().isAssignableFrom(String.class)) {
                                if (!StringUtils.isEmpty(excelField.select())) {
                                    for (SysDictVo sysDictVo : sysDictVos) {
                                        if (sysDictVo.getDictType().equals(excelField.select()) && Objects.equals(value, sysDictVo.getDictName())) {
                                            value = sysDictVo.getDictId();
                                        }
                                    }
                                } else if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = value.toString();
                                } else {
                                    value = null;
                                }
                            }


                            field.set(t, value);
                        } catch (Exception e) {
                            log.error("第几行{}{}", i, excelField.fieldCN());
                            e.printStackTrace();
                        }
                    }
                }
            }
            list.add(t);
        }

        return list;
    }


    /**
     * 导入
     *
     * @param clz        类型
     * @param sheet      。。
     * @param rowIndex   起始行 0 开始
     * @param lastRowNum 最后一行
     * @return
     * @throws NoSuchFieldException 没有列
     */
    public static <T> List<T> impZt(Class<?> clz, Sheet sheet, final int rowIndex, int lastRowNum, Workbook workbook, List<SysDictVo> sysDictVos) throws NoSuchFieldException, IllegalAccessException, InstantiationException, ParseException {

        List<T> list = new ArrayList<>();

        Field[] fields = clz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (excelField != null && excelField.imp()) {
                // 执行导出流程。。。。。。。。。。。。 1 3 5
                fieldNames[excelField.impOrder()] = field.getName();
            }
        }
        FormulaEvaluator hssfFormulaEvaluator;
        if (workbook instanceof HSSFWorkbook) {
            hssfFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        } else {
            hssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        }

        // 读取
        for (int i = rowIndex; i < lastRowNum; i++) {
            T t = (T) clz.newInstance();
            for (int i1 = 0; i1 < fieldNames.length; i1++) {
                String fieldName = fieldNames[i1];
                if (!StringUtils.isEmpty(fieldName)) {
                    Field field = clz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    ExcelField excelField = field.getAnnotation(ExcelField.class);
                    Row row = sheet.getRow(i);
                    Cell cell = row.getCell(excelField.impOrder());


                    if (cell != null) {
                        CellStyle cellStyle = cell.getCellStyle();
                        Color xssfColor = cellStyle.getFillForegroundColorColor();
                        if (xssfColor == null && !Objects.equals(field.getName(), "objId")) {
                            // 取中台
                        } else {
                            Object value = null;
                            switch (cell.getCellType()) {
                                case STRING:
                                    value = cell.getStringCellValue();
                                    break;
                                case NUMERIC:
//                                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
//                                    value = cell.getNumericCellValue();
//                                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                                    value = cell.getNumericCellValue();
//                                } else {
//                                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue());
//                                    SimpleDateFormat f = new SimpleDateFormat("yyyy/MM/dd");
//                                    //  特定格式的日期
//                                    value = f.parse(f.format(date));
//                                }
                                    value = cell.getNumericCellValue();
                                    break;
                                case BOOLEAN:
                                    value = cell.getBooleanCellValue();
                                    break;
                                case FORMULA:
                                    try {
                                        CellValue cellValue = hssfFormulaEvaluator.evaluate(cell);
                                        if (cellValue.getCellType() == CellType.NUMERIC) {
//                                        value = cellValue.getNumberValue();
                                            String s = Double.toString(cellValue.getNumberValue());
                                            value = new BigDecimal(s).setScale(excelField.scale(), RoundingMode.DOWN);
                                        } else if (cellValue.getCellType() == CellType.STRING) {
                                            value = cellValue.getStringValue();
                                        }
                                    } catch (IllegalStateException e) {
                                        e.printStackTrace();
                                    }
                                    break;
                                case BLANK:
                                    value = null;
                                    break;
                                default:
                                    value = cell.toString();
                            }
                            try {
                                if (field.getType().isAssignableFrom(Date.class)) {
                                    value = value == null ? null : DoubleToDate((double) value);
                                } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                                    if (value != null && !StringUtils.isEmpty(value.toString())) {
                                        value = new BigDecimal(value.toString().equals("-") ? "0" : value.toString());
                                    } else {
                                        value = null;
                                    }
                                } else if (field.getType().isAssignableFrom(Double.class)) {
                                    if (value != null && !StringUtils.isEmpty(value.toString())) {
                                        value = Double.parseDouble(value.toString());
                                    } else {
                                        value = null;
                                    }
                                } else if (field.getType().isAssignableFrom(String.class)) {
                                    if (!StringUtils.isEmpty(excelField.select())) {
                                        for (SysDictVo sysDictVo : sysDictVos) {
                                            if (sysDictVo.getDictType().equals(excelField.select()) && Objects.equals(value, sysDictVo.getDictName())) {
                                                value = sysDictVo.getDictId();
                                            }
                                        }
                                    } else if (value != null && !StringUtils.isEmpty(value.toString())) {
                                        value = value.toString();
                                    } else {
                                        value = null;
                                    }
                                }
// TODO 如果包含中台 设置为 null。。。。。。。。。。。
                                field.set(t, value);
                            } catch (Exception e) {
                                log.error("第几行{}{}", i, excelField.fieldCN());
                                e.printStackTrace();
                            }
                        }

                    }
                }
            }
            list.add(t);
        }

        return list;
    }


    /**
     * 颜色 + 内容
     *
     * @param editClz  类型
     * @param sheet    。。
     * @param rowIndex 起始行 0 开始
     * @throws NoSuchFieldException 没有列
     */
    public static <R, S> void impZtOne(Class<?> editClz, Class<?> colorClz, List<R> editList, List<S> colorList,
                                       Sheet sheet, final int rowIndex, Workbook workbook,
                                       List<SysDictVo> sysDictVos) throws NoSuchFieldException, IllegalAccessException, InstantiationException {
        Field[] fields = editClz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (excelField != null && excelField.imp()) {
                // 执行导出流程。。。。。。。。。。。。 1 3 5
                fieldNames[excelField.impOrder()] = field.getName();
            }
        }
        FormulaEvaluator hssfFormulaEvaluator;
        int lastRowNum = sheet.getLastRowNum();
        if (workbook instanceof HSSFWorkbook) {
            hssfFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        } else {
            hssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        }
        // 读取
        for (int i = rowIndex; i <= lastRowNum; i++) {
            R editEntity = (R) editClz.newInstance();
            S colorEntity = (S) colorClz.newInstance();
            for (int i1 = 0; i1 < fieldNames.length; i1++) {
                String fieldName = fieldNames[i1];
                if (!StringUtils.isEmpty(fieldName)) {
                    Field editField = editClz.getDeclaredField(fieldName);
                    editField.setAccessible(true);

                    Field colorField = colorClz.getDeclaredField(fieldName);
                    colorField.setAccessible(true);

                    ExcelField excelField = editField.getAnnotation(ExcelField.class);
                    Row row = sheet.getRow(i);
                    Cell cell = row.getCell(excelField.impOrder());

                    if (cell != null) {
                        Object value = null;
                        switch (cell.getCellType()) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                value = cell.getNumericCellValue();
                                break;
                            case BOOLEAN:
                                value = cell.getBooleanCellValue();
                                break;
                            case FORMULA:
                                try {
                                    CellValue cellValue = hssfFormulaEvaluator.evaluate(cell);
                                    if (cellValue.getCellType() == CellType.NUMERIC) {
                                        String s = Double.toString(cellValue.getNumberValue());
                                        value = new BigDecimal(s).setScale(excelField.scale(), RoundingMode.DOWN);
                                    } else if (cellValue.getCellType() == CellType.STRING) {
                                        value = cellValue.getStringValue();
                                    }
                                } catch (IllegalStateException e) {
                                    e.printStackTrace();
                                }
                                break;
                            case BLANK:
                                break;
                            default:
                                value = cell.toString();
                        }
                        try {
                            if (editField.getType().isAssignableFrom(Date.class)) {
                                value = value == null ? null : DoubleToDate((double) value);
                            } else if (editField.getType().isAssignableFrom(BigDecimal.class)) {
                                if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = new BigDecimal(value.toString().equals("-") ? "0" : value.toString());
                                } else {
                                    value = null;
                                }
                            } else if (editField.getType().isAssignableFrom(Double.class)) {
                                if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = Double.parseDouble(value.toString());
                                } else {
                                    value = null;
                                }
                            } else if (editField.getType().isAssignableFrom(String.class)) {
                                if (!StringUtils.isEmpty(excelField.select())) {
                                    for (SysDictVo sysDictVo : sysDictVos) {
                                        if (sysDictVo.getDictType().equals(excelField.select()) && Objects.equals(value, sysDictVo.getDictName())) {
                                            value = sysDictVo.getDictId();
                                        }
                                    }
                                } else if (value != null && !StringUtils.isEmpty(value.toString())) {
                                    value = value.toString();
                                } else {
                                    value = null;
                                }
                            }

                            // Color xssfColor = cell.getCellStyle().getFillForegroundColorColor();

                            if (editField.getName().equals("objId") || editField.getName().equals("mid")) {
                                if (value == null || Objects.equals("", value)) {
//                                    String objId = UUID.randomUUID().toString();
//                                    editField.set(editEntity, objId);
//                                    colorField.set(colorEntity, objId);
                                } else {
                                    editField.set(editEntity, value);
                                    colorField.set(colorEntity, value);
                                }
                            } else if (value == null || (
                                    value instanceof String && StringUtils.isEmpty((String) value))) {
                                // 取中台
                                if (colorField.getType().isAssignableFrom(Date.class)) {
                                    colorField.set(colorEntity, value);
                                    editField.set(editEntity, value);
                                } else {
                                    colorField.set(colorEntity, "");
                                    editField.set(editEntity, null);
                                }
                            } else {
                                // 取本地
                                colorField.set(colorEntity, "green");
                                editField.set(editEntity, value);
                            }
                        } catch (Exception e) {
                            log.error("第几行{}{}", i, excelField.fieldCN());
                            e.printStackTrace();
                        }

                    }
                }
            }
            editList.add(editEntity);
            colorList.add(colorEntity);
        }

    }

    public static Date ExcelDoubleToDate(String strDate) {
        Date tDate = new Date();
        if (strDate.length() == 5) {
            tDate = DoubleToDate(Double.parseDouble(strDate));
        }
        return tDate;
    }

    /*
     * 解析Excel日期格式
     */
    public static Date DoubleToDate(Double dVal) {
        Date tDate = new Date();
        long localOffset = tDate.getTimezoneOffset() * 60000; // 系统时区偏移 1900/1/1
        // 到 1970/1/1 的
        // 25569 天
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));
        return tDate;
    }


    /**
     * 刷新
     */
    public void refresh(int startRow, int endRow, int startCol, int endCol, Sheet sheet) {
        for (int i = startRow; i < endRow; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                for (int i1 = startCol; i1 < endCol; i1++) {
                    Cell cell = row.getCell(i1);
                    if (cell != null) {
                        if (cell.getCellType() == CellType.FORMULA) {
                            cell.setCellFormula(cell.getCellFormula());
                        }
                    }
                }
            }
        }
    }


    /**
     * 导出
     *
     * @param list          数据
     * @param clz           类型
     * @param titleRowIndex 标题行
     * @param sheet         。。
     * @param rowIndex      起始行 0 开始
     * @throws NoSuchFieldException 没有列
     */
    @SuppressWarnings({"java:S3011"})
    public static <T> void exp(List<T> list, Class<?> clz, Sheet sheet, final int rowIndex, CellStyle cellStyle, final int titleRowIndex, List<SysDictVo> sysDictVos, String tableHeadFields, int size, List<CellStyle> dateFormat) {
        List<String> strings = null;
        if (tableHeadFields != null) {
            String[] split = tableHeadFields.split(",");
            strings = Arrays.asList(split);
        }
        Field[] fields = clz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (strings == null) {
                if (excelField != null && excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            } else {
                if (excelField != null && excelField.exp() && strings.contains(field.getName())) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            }
        }


        // 要不要弄成set..

        List<MethodVO> methods = Lists.newArrayList();
        for (int i = 0; i < fieldNames.length; i++) {
            String fieldName = fieldNames[i];
            if (!StringUtils.isEmpty(fieldName)) {
                try {
                    Field field = clz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    ExcelField excelField = field.getAnnotation(ExcelField.class);
                    String getMethodName = "get" + HumpLineUtil.firstUpper(fieldName);
                    Method getMethod = clz.getMethod(getMethodName);
                    methods.add(MethodVO.builder().fieldCN(excelField.fieldCN()).dateFormat(excelField.dateFormat()).colIndex(i)
                            .method(getMethod).cls(getMethod.getReturnType()).select(excelField.select()).build());
                } catch (NoSuchFieldException | NoSuchMethodException e) {
                    e.printStackTrace();
                }
            }
        }

// 表头
        for (MethodVO methodVO : methods) {
            int colIndex = methodVO.getColIndex();
            if (titleRowIndex >= 0) {
                Row titleRow = sheet.getRow(titleRowIndex);
                if (titleRow == null) {
                    titleRow = sheet.createRow(titleRowIndex);
                }
                Cell titleCell = titleRow.getCell(colIndex);
                if (titleCell == null) {
                    titleCell = titleRow.createCell(colIndex);
                }
                titleCell.setCellValue(methodVO.getFieldCN());
            }

            if (!StringUtils.isEmpty(methodVO.getSelect())) {
                // 设置那一列 改为下拉。。。。。。。。。
                List<String> tt = new ArrayList<>();
                for (SysDictVo sysDictVo : sysDictVos) {
                    if (sysDictVo.getDictType().equals(methodVO.getSelect())) {
                        tt.add(sysDictVo.getDictName());
                    }
                }
                CellRangeAddressList regions = new CellRangeAddressList(rowIndex, rowIndex + size, colIndex, colIndex);
                dropDownBox(tt.toArray(new String[]{}), regions, sheet);
            }

            Class<?> type = methodVO.getCls();
            if (type.isAssignableFrom(Date.class)) {
                CellRangeAddressList regions = new CellRangeAddressList(rowIndex, rowIndex + size, colIndex, colIndex);
                dateBox(regions, sheet);
            }
        }

        for (int i = 0; i < list.size(); i++) {
            T t = list.get(i);
            // 这个需要按行写。。。
            for (MethodVO methodVO : methods) {
                int colIndex = methodVO.getColIndex();
                Class<?> type = methodVO.getCls();
                Row row = sheet.getRow(i + rowIndex);
                // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
                if (row == null) {
                    row = sheet.createRow(i + rowIndex);
                }
                Cell cell = row.getCell(colIndex);
                if (cell == null) {
                    cell = row.createCell(colIndex);
                }
                if (cellStyle != null) {
                    cell.setCellStyle(cellStyle);
                }
                try {
                    Object invoke = methodVO.getMethod().invoke(t);
                    if (invoke == null) {

                    } else if (type.isAssignableFrom(Date.class)) {
                        cell.setCellValue((Date) invoke);
                        cell.setCellStyle(dateFormat.get(methodVO.getDateFormat()));
                    } else if (type.isAssignableFrom(BigDecimal.class)) {
                        cell.setCellValue(((BigDecimal) invoke).doubleValue());
                    } else if (type.isAssignableFrom(Double.class)) {
                        cell.setCellValue((Double) invoke);
                    } else {
                        cell.setCellValue(invoke.toString());
                    }
                } catch (IllegalAccessException | InvocationTargetException e) {
                    e.printStackTrace();
                }
            }

            if (i % 100 == 0) {
                try {
                    if (sheet instanceof SXSSFSheet) {
                        ((SXSSFSheet) sheet).flushRows(100);
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    @SuppressWarnings({"java:S3011"})
    public static <T> void exp(List<T> list, Class<?> clz, Sheet sheet, final int rowIndex, CellStyle cellStyle, final int titleRowIndex, List<SysDictVo> sysDictVos, String tableHeadFields, int size, List<CellStyle> dateFormat, CellStyle green) throws NoSuchFieldException, IllegalAccessException {
        List<String> strings = null;
        if (tableHeadFields != null) {
            String[] split = tableHeadFields.split(",");
            strings = Arrays.asList(split);
        }
        Field[] fields = clz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (strings == null) {
                if (excelField != null && excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            } else {
                if (excelField != null && excelField.exp() && strings.contains(field.getName())) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            }
        }

        int colIndex = 0;
        // 用于计数
        int rowIndexs = rowIndex;
        for (String fieldName : fieldNames) {
            if (!StringUtils.isEmpty(fieldName)) {
                Field field = clz.getDeclaredField(fieldName);
                field.setAccessible(true);
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                String expColor = excelField.expColor();
                if (excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
                if (titleRowIndex >= 0) {
                    Row titleRow = sheet.getRow(titleRowIndex);
                    if (titleRow == null) {
                        titleRow = sheet.createRow(titleRowIndex);
                    }
                    Cell titleCell = titleRow.getCell(colIndex);
                    if (titleCell == null) {
                        titleCell = titleRow.createCell(colIndex);
                    }
                    titleCell.setCellValue(excelField.fieldCN());
                }


                if (!StringUtils.isEmpty(excelField.select())) {
                    // 设置那一列 改为下拉。。。。。。。。。
                    List<String> tt = new ArrayList<>();
                    for (SysDictVo sysDictVo : sysDictVos) {
                        if (sysDictVo.getDictType().equals(excelField.select())) {
                            tt.add(sysDictVo.getDictName());
                        }
                    }
                    CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, rowIndexs + size, colIndex, colIndex);
                    dropDownBox(tt.toArray(new String[]{}), regions, sheet);
                }


                if (field.getType().isAssignableFrom(Date.class)) {
                    CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, rowIndexs + size, colIndex, colIndex);
                    dateBox(regions, sheet);
                }


                for (T t : list) {
                    Row row = sheet.getRow(rowIndexs);
                    // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
                    if (row == null) {
                        row = sheet.createRow(rowIndexs);
                    }
                    Cell cell = row.getCell(colIndex);
                    if (cell == null) {
                        cell = row.createCell(colIndex);
                    }


                    if (!StringUtils.isEmpty(expColor)) {
                        cell.setCellStyle(green);
                    } else if (cellStyle != null) {
                        cell.setCellStyle(cellStyle);
                    }


                    if (field.get(t) == null) {

                    } else if (field.getType().isAssignableFrom(Date.class)) {
                        cell.setCellValue((Date) field.get(t));
                        cell.setCellStyle(dateFormat.get(excelField.dateFormat()));
                    } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                        cell.setCellValue(((BigDecimal) field.get(t)).doubleValue());
                    } else if (field.getType().isAssignableFrom(Double.class)) {
                        cell.setCellValue((Double) field.get(t));
                    } else {
                        cell.setCellValue(field.get(t).toString());
                    }
                    rowIndexs++;
                }
                rowIndexs = rowIndex;
                colIndex++;
            }
        }
    }

    /**
     * 导出
     *
     * @param list          数据
     * @param clz           类型
     * @param titleRowIndex 标题行
     * @param sheet         。。
     * @param rowIndex      起始行 0 开始
     * @throws NoSuchFieldException 没有列
     */
    @SuppressWarnings({"java:S3011"})
    public static <T> void exp2(List<T> list, Class<?> clz, Sheet sheet, final int rowIndex, CellStyle cellStyle, final int titleRowIndex, String tableHeadFields, int size, List<CellStyle> dateFormat) throws NoSuchFieldException, IllegalAccessException {
        List<String> strings = null;
        if (tableHeadFields != null) {
            String[] split = tableHeadFields.split(",");
            strings = Arrays.asList(split);
        }
        Field[] fields = clz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (strings == null) {
                if (excelField != null && excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            } else {
                if (excelField != null && excelField.exp() && strings.contains(field.getName())) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            }
        }

        int colIndex = 0;
        // 用于计数
        int rowIndexs = rowIndex;
        for (String fieldName : fieldNames) {
            if (!StringUtils.isEmpty(fieldName)) {
                Field field = clz.getDeclaredField(fieldName);
                field.setAccessible(true);
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                if (excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }

                //新加判断>=0
                if (titleRowIndex >= 0) {
                    Row titleRow = sheet.getRow(titleRowIndex);
                    if (titleRow == null) {
                        titleRow = sheet.createRow(titleRowIndex);
                    }
                    Cell titleCell = titleRow.getCell(colIndex);
                    if (titleCell == null) {
                        titleCell = titleRow.createCell(colIndex);
                    }
                    titleCell.setCellValue(excelField.fieldCN());
                }

                for (T t : list) {
                    Row row = sheet.getRow(rowIndexs);
                    // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
                    if (row == null) {
                        row = sheet.createRow(rowIndexs);
                    }
                    Cell cell = row.getCell(colIndex);
                    if (cell == null) {
                        cell = row.createCell(colIndex);
                    }
                    if (cellStyle != null) {
                        cell.setCellStyle(cellStyle);
                    }


                    if (field.get(t) == null) {

                    } else if (field.getType().isAssignableFrom(Date.class)) {
                        cell.setCellValue((Date) field.get(t));
                        cell.setCellStyle(dateFormat.get(excelField.dateFormat()));
                    } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                        cell.setCellValue(((BigDecimal) field.get(t)).doubleValue());
                    } else if (field.getType().isAssignableFrom(Double.class)) {
                        cell.setCellValue((Double) field.get(t));
                    } else {
                        cell.setCellValue(field.get(t).toString());
                    }
                    rowIndexs++;
                }
                rowIndexs = rowIndex;
                colIndex++;
            }
        }
    }


    /**
     * 导出结果(多列。。。。。。。。。。。。)
     */
    @SuppressWarnings({"java:S3011"})
    public void expMap(List<Map<String, Object>> list, Class<?> clz, HSSFSheet sheet, final int rowIndex, CellStyle cellStyle, final int titleRowIndex, List<SysDictVo> sysDictVos, String tableHeadFields, int size, List<CellStyle> dateFormat

            , CellStyle redStyle, CellStyle greenStyle


    ) throws NoSuchFieldException {
        List<String> strings = null;
        if (tableHeadFields != null) {
            String[] split = tableHeadFields.split(",");
            strings = Arrays.asList(split);
        }
        Field[] fields = clz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (strings == null) {
                if (excelField != null && excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            } else {
                if (excelField != null && excelField.exp() && strings.contains(field.getName())) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            }
        }

        int colIndex = 0;
        // 用于计数
        int rowIndexs = rowIndex;
        for (String fieldName : fieldNames) {
            if (!StringUtils.isEmpty(fieldName)) {
                Field field = clz.getDeclaredField(fieldName);
                field.setAccessible(true);
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                MiddleGround middleGround = field.getAnnotation(MiddleGround.class);
                if (excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
                // 0 2 4 6 8 2n 2n+1


                HSSFRow titleRow = sheet.getRow(titleRowIndex);
                if (titleRow == null) {
                    titleRow = sheet.createRow(titleRowIndex);
                }


                if (!Objects.equals(field.getName(), "objId") && !Objects.equals(field.getName(), "mid")) {
                    col2n(list, sheet, cellStyle, sysDictVos, size, dateFormat, redStyle, greenStyle, colIndex * 2 + 1, rowIndexs, field, excelField, titleRow, middleGround);
                    rowIndexs = rowIndex;
                    col2n1(list, sheet, cellStyle, sysDictVos, size, dateFormat, redStyle, greenStyle, colIndex * 2 + 2, rowIndexs, field, excelField, titleRow);
                    rowIndexs = rowIndex;
                    colIndex++;
                } else {
                    HSSFCell titleCell = titleRow.getCell(colIndex);
                    if (titleCell == null) {
                        titleCell = titleRow.createCell(colIndex);
                    }

                    if (Objects.equals(field.getName(), "objId")) {
                        titleCell.setCellValue("obj_id");
                    }

                    if (Objects.equals(field.getName(), "mid")) {
                        titleCell.setCellValue("mid");
                    }


                    for (Map<String, Object> t : list) {
                        HSSFRow row = sheet.getRow(rowIndexs);
                        // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
                        if (row == null) {
                            row = sheet.createRow(rowIndexs);
                        }
                        HSSFCell cell = row.getCell(colIndex);
                        if (cell == null) {
                            cell = row.createCell(colIndex);
                        }

                        Object o = t.get(field.getName());

                        cell.setCellStyle(cellStyle);


                        if (o == null) {

                        } else if (field.getType().isAssignableFrom(Date.class)) {
                            cell.setCellValue((Date) o);
                            cell.setCellStyle(dateFormat.get(excelField.dateFormat()));
                        } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                            cell.setCellValue(((BigDecimal) o).doubleValue());
                        } else if (field.getType().isAssignableFrom(Double.class)) {
                            cell.setCellValue((Double) o);
                        } else {
                            cell.setCellValue(o.toString());
                        }
                        rowIndexs++;
                    }
                    rowIndexs = rowIndex;
                }

            }
        }
    }


    /**
     * 导出结果(多列。。。。。。。。。。。。)
     */
    @SuppressWarnings({"java:S3011"})
    public void expMapOne(List<Map<String, Object>> list, Class<?> clz, Sheet sheet, final int rowIndex, CellStyle cellStyle, final int titleRowIndex, List<SysDictVo> sysDictVos, String tableHeadFields, int size, List<CellStyle> dateFormat
            , CellStyle redStyle, CellStyle greenStyle
    ) throws NoSuchFieldException {
        List<String> strings = null;
        if (tableHeadFields != null) {
            String[] split = tableHeadFields.split(",");
            strings = Arrays.asList(split);
        }
        Field[] fields = clz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (strings == null) {
                if (excelField != null && excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            } else {
                if (excelField != null && excelField.exp() && strings.contains(field.getName())) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            }
        }

        int colIndex = 0;
        // 用于计数
        int rowIndexs = rowIndex;
        for (String fieldName : fieldNames) {
            if (!StringUtils.isEmpty(fieldName)) {
                Field field = clz.getDeclaredField(fieldName);
                field.setAccessible(true);
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                MiddleGround middleGround = field.getAnnotation(MiddleGround.class);
                if (excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
                // 0 2 4 6 8 2n 2n+1


                Row titleRow = sheet.getRow(titleRowIndex);
                if (titleRow == null) {
                    titleRow = sheet.createRow(titleRowIndex);
                }

                col(list, sheet, cellStyle, sysDictVos, size, dateFormat, redStyle, greenStyle, colIndex, rowIndexs, field, excelField, titleRow, middleGround);
                rowIndexs = rowIndex;
                colIndex++;
            }
        }
    }


    /**
     * 导出模板(多列。。。)
     *
     * @param titleRowIndex 标题行
     * @param rowIndex      起始行 0 开始
     * @throws NoSuchFieldException 没有列
     */
    @SuppressWarnings({"java:S3011"})
    public static <R, S> void expResult(List<R> editList, List<S> colorList, Class<?> rclz, Class<?> sclz, Sheet sheet, final int rowIndex, CellStyle cellStyle, CellStyle red, CellStyle green
            , final int titleRowIndex, List<SysDictVo> sysDictVos, String tableHeadFields, int size, List<CellStyle> dateFormat
            , boolean... addTitle
    ) throws NoSuchFieldException, IllegalAccessException {
        List<String> strings = null;
        if (tableHeadFields != null) {
            String[] split = tableHeadFields.split(",");
            strings = Arrays.asList(split);
        }
        Field[] fields = rclz.getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (Field field : fields) {
            field.setAccessible(true);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (strings == null) {
                if (excelField != null && excelField.exp()) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            } else {
                if (excelField != null && excelField.exp() && strings.contains(field.getName())) {
                    // 执行导出流程。。。。。。。。。。。。 1 3 5
                    fieldNames[excelField.expOrder()] = field.getName();
                }
            }
        }
        int colIndex = 0;
        if (addTitle.length != 0 && addTitle[0]) {
            Row titleRow = sheet.createRow(titleRowIndex);
            for (String fieldName : fieldNames) {
                if (!StringUtils.isEmpty(fieldName)) {
                    Field field = rclz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    ExcelField excelField = field.getAnnotation(ExcelField.class);
                    Cell titleCell = titleRow.createCell(colIndex);
                    titleCell.setCellValue(excelField.fieldCN());
                    colIndex++;
                }
            }
        }

        colIndex = 0;
        // 用于计数
        int rowIndexs = rowIndex;
        for (int i = 0; i < editList.size(); i++) {
            R edit = editList.get(i);
            S color = colorList.get(i);
            Row row = sheet.createRow(rowIndexs);
            for (String fieldName : fieldNames) {
                if (!StringUtils.isEmpty(fieldName)) {
                    Field field = rclz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    ExcelField excelField = field.getAnnotation(ExcelField.class);
                    if (excelField.exp()) {
                        // 执行导出流程。。。。。。。。。。。。 1 3 5
                        fieldNames[excelField.expOrder()] = field.getName();
                    }

                    if (!StringUtils.isEmpty(excelField.select())) {
                        // 设置那一列 改为下拉。。。。。。。。。
                        List<String> tt = new ArrayList<>();
                        for (SysDictVo sysDictVo : sysDictVos) {
                            if (sysDictVo.getDictType().equals(excelField.select())) {
                                tt.add(sysDictVo.getDictName());
                            }
                        }
                        CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, rowIndexs + size, colIndex, colIndex);
                        dropDownBox(tt.toArray(new String[]{}), regions, sheet);
                    }


                    if (field.getType().isAssignableFrom(Date.class)) {
                        CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, rowIndexs + size, colIndex, colIndex);
                        dateBox(regions, sheet);
                    }
///////////////


                    Cell cell = row.createCell(colIndex);

                    Object o = field.get(edit);
                    Field colorField = sclz.getDeclaredField(fieldName);
                    System.out.println(colorField.getName());
                    colorField.setAccessible(true);
                    Object colorObj = colorField.get(color);
                    if (colorObj == null || Objects.equals(colorObj, "")) {
                        cell.setCellStyle(cellStyle);
                    } else if (Objects.equals(colorObj, "green")) {
                        cell.setCellStyle(green);
                    } else if (Objects.equals(colorObj, "red")) {
                        cell.setCellStyle(red);
                    }

                    if (o == null) {

                    } else if (field.getType().isAssignableFrom(Date.class)) {
                        if (o instanceof LocalDateTime) {
                            LocalDateTime o1 = (LocalDateTime) o;
                            // 将此日期时间与时区相结合以创建 ZonedDateTime
                            ZonedDateTime zonedDateTime = o1.atZone(ZoneId.systemDefault());
                            // 本地时间线LocalDateTime到即时时间线Instant时间戳
                            Instant instant = zonedDateTime.toInstant();
                            // UTC时间(世界协调时间,UTC + 00:00)转北京(北京,UTC + 8:00)时间
                            Date date = Date.from(instant);
                            cell.setCellValue(date);
                            cell.setCellStyle(dateFormat.get(excelField.dateFormat()));
                        } else if (o instanceof Date) {
                            cell.setCellValue((Date) o);
                            cell.setCellStyle(dateFormat.get(excelField.dateFormat()));
                        }
                    } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                        cell.setCellValue(((BigDecimal) o).doubleValue());
                    } else if (field.getType().isAssignableFrom(Double.class)) {
                        cell.setCellValue((Double) o);
                    } else {
                        cell.setCellValue(o.toString());
                    }

                    colIndex++;
                }
            }
            colIndex = 0;
            rowIndexs++;
        }
    }

    private void col(List<Map<String, Object>> list, Sheet sheet, CellStyle cellStyle, List<SysDictVo> sysDictVos, int size, List<CellStyle> dateFormat, CellStyle redStyle, CellStyle greenStyle, int colIndex, int rowIndexs, Field field, ExcelField excelField, Row titleRow, MiddleGround middleGround) {
        Cell titleCell = titleRow.getCell(colIndex);
        if (titleCell == null) {
            titleCell = titleRow.createCell(colIndex);
        }

        if (middleGround != null) {
            titleCell.setCellValue(excelField.fieldCN() + "(中台取值)");
        } else {
            titleCell.setCellValue(excelField.fieldCN() + "(未从中台取值)");
        }


        if (!StringUtils.isEmpty(excelField.select())) {
            // 设置那一列 改为下拉。。。。。。。。。
            List<String> tt = new ArrayList<>();
            for (SysDictVo sysDictVo : sysDictVos) {
                if (sysDictVo.getDictType().equals(excelField.select())) {
                    tt.add(sysDictVo.getDictName());
                }
            }
            CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, size, colIndex, colIndex);
            dropDownBox(tt.toArray(new String[]{}), regions, sheet);
        }


        if (field.getType().isAssignableFrom(Date.class)) {
            CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, size, colIndex, colIndex);
            dateBox(regions, sheet);
        }


        for (Map<String, Object> t : list) {
            Row row = sheet.getRow(rowIndexs);
            // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
            if (row == null) {
                row = sheet.createRow(rowIndexs);
            }
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                cell = row.createCell(colIndex);
            }

            Object o = t.get(field.getName());
            if (Objects.equals(field.getName(), "objId")) {
                o = t.get("id");
            }

            if (o != null) {
                Object o1 = t.get(field.getName() + "___state");
                if (Objects.equals("red", o1)) {
                    cell.setCellStyle(redStyle);
                } else if (Objects.equals("green", o1)) {
                    cell.setCellStyle(greenStyle);
                } else {
                    cell.setCellStyle(cellStyle);
                }
            }


            if (o == null) {

            } else if (field.getType().isAssignableFrom(Date.class)) {
                cell.setCellValue((Date) o);
                cell.setCellStyle(dateFormat.get(excelField.dateFormat()));
            } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                cell.setCellValue(((BigDecimal) o).doubleValue());
            } else if (field.getType().isAssignableFrom(Double.class)) {
                cell.setCellValue((Double) o);
            } else {
                cell.setCellValue(o.toString());
            }
            rowIndexs++;
        }
    }


    private void col2n(List<Map<String, Object>> list, HSSFSheet sheet, CellStyle cellStyle, List<SysDictVo> sysDictVos, int size, List<CellStyle> dateFormat, CellStyle redStyle, CellStyle greenStyle, int colIndex, int rowIndexs, Field field, ExcelField excelField, HSSFRow titleRow, MiddleGround middleGround) {
        HSSFCell titleCell = titleRow.getCell(colIndex);
        if (titleCell == null) {
            titleCell = titleRow.createCell(colIndex);
        }
//        titleCell.setCellValue(excelField.fieldCN());

        if (middleGround != null) {
            titleCell.setCellValue(excelField.fieldCN() + "(中台取值)");
        } else {
            titleCell.setCellValue(excelField.fieldCN() + "(未从中台取值)");
        }


        if (!StringUtils.isEmpty(excelField.select())) {
            // 设置那一列 改为下拉。。。。。。。。。
            List<String> tt = new ArrayList<>();
            for (SysDictVo sysDictVo : sysDictVos) {
                if (sysDictVo.getDictType().equals(excelField.select())) {
                    tt.add(sysDictVo.getDictName());
                }
            }
            CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, size, colIndex, colIndex);
            dropDownBox(tt.toArray(new String[]{}), regions, sheet);
        }


        if (field.getType().isAssignableFrom(Date.class)) {
            CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, size, colIndex, colIndex);
            dateBox(regions, sheet);
        }


        for (Map<String, Object> t : list) {
            HSSFRow row = sheet.getRow(rowIndexs);
            // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
            if (row == null) {
                row = sheet.createRow(rowIndexs);
            }
            HSSFCell cell = row.getCell(colIndex);
            if (cell == null) {
                cell = row.createCell(colIndex);
            }

            Object o = t.get(field.getName() + "___zhongtai");

            if (o != null) {
                Object o1 = t.get(field.getName() + "___state");
                if (Objects.equals("red", o1)) {
                    cell.setCellStyle(redStyle);
                } else if (Objects.equals("green", o1)) {
                    cell.setCellStyle(greenStyle);
                } else {
                    cell.setCellStyle(cellStyle);
                }
            }


            if (o == null) {

            } else if (field.getType().isAssignableFrom(Date.class)) {
                cell.setCellValue((Date) o);
                cell.setCellStyle(dateFormat.get(excelField.dateFormat()));
            } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                cell.setCellValue(((BigDecimal) o).doubleValue());
            } else if (field.getType().isAssignableFrom(Double.class)) {
                cell.setCellValue((Double) o);
            } else {
                cell.setCellValue(o.toString());
            }
            rowIndexs++;
        }
    }

    private void col2n1(List<Map<String, Object>> list, HSSFSheet sheet, CellStyle cellStyle, List<SysDictVo> sysDictVos, int size, List<CellStyle> dateFormat, CellStyle redStyle, CellStyle greenStyle, int colIndex, int rowIndexs, Field field, ExcelField excelField, HSSFRow titleRow) {
        HSSFCell titleCell = titleRow.getCell(colIndex);
        if (titleCell == null) {
            titleCell = titleRow.createCell(colIndex);
        }

        titleCell.setCellValue(excelField.fieldCN() + "(修改值)");


        if (!StringUtils.isEmpty(excelField.select())) {
            // 设置那一列 改为下拉。。。。。。。。。
            List<String> tt = new ArrayList<>();
            for (SysDictVo sysDictVo : sysDictVos) {
                if (sysDictVo.getDictType().equals(excelField.select())) {
                    tt.add(sysDictVo.getDictName());
                }
            }
            CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, size, colIndex, colIndex);
            dropDownBox(tt.toArray(new String[]{}), regions, sheet);
        }


        if (field.getType().isAssignableFrom(Date.class)) {
            CellRangeAddressList regions = new CellRangeAddressList(rowIndexs, size, colIndex, colIndex);
            dateBox(regions, sheet);
        }


        for (Map<String, Object> t : list) {
            HSSFRow row = sheet.getRow(rowIndexs);
            // 创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
            if (row == null) {
                row = sheet.createRow(rowIndexs);
            }
            HSSFCell cell = row.getCell(colIndex);
            if (cell == null) {
                cell = row.createCell(colIndex);
            }

            Object o = t.get(field.getName() + "___bendi");

            if (o != null) {
                Object o1 = t.get(field.getName() + "___state");
                if (Objects.equals("red", o1)) {
                    cell.setCellStyle(redStyle);
                } else if (Objects.equals("green", o1)) {
                    cell.setCellStyle(greenStyle);
                } else {
                    cell.setCellStyle(cellStyle);
                }
            }


            if (o == null) {

            } else if (field.getType().isAssignableFrom(Date.class)) {
                cell.setCellValue((Date) o);
                cell.setCellStyle(dateFormat.get(excelField.dateFormat()));
            } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                cell.setCellValue(((BigDecimal) o).doubleValue());
            } else if (field.getType().isAssignableFrom(Double.class)) {
                cell.setCellValue((Double) o);
            } else {
                cell.setCellValue(o.toString());
            }
            rowIndexs++;
        }
    }

    public static void setResponseHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()));
        response.setHeader("file", URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()));
        response.setHeader("Access-Control-Expose-Headers", "file");
    }


    public static void merge(Sheet sheet, int col) {
        int lastRowNum = sheet.getLastRowNum();
        String last = null;
        String current = null;
        for (int i = 4; i < lastRowNum; i++) {
            current = GyExcelUtil.readXY(sheet, 0, i);
            if (Objects.equals(last, current)) {
                // 合并
                    sheet.addMergedRegion(new CellRangeAddress(i - 1, i, col, col));
            }
            last = current;
        }

    }
}

@Data
@Builder
class MethodVO {
    private Method method;
    private Class<?> cls;
    private String select;
    private String fieldCN;
    private int dateFormat;
    private int colIndex;
}
